/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.appspot.trick.it.admin.db;

import com.appspot.trick.it.admin.Constants;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author takahara
 */
public class DBManager {
    
    private static final String OAUTH_DATA_KEY = "oauth_data_key";
    
    private static Connection conn;

    /**
     * コネクションの取得
     *
     * @return Connection
     * @throws java.sql.SQLException
     */
    public static Connection getConn() throws SQLException {
        
        if(conn != null && !conn.isClosed()) {
            return conn;
        }
        
        try {
            // 自動生成しない
            conn = DriverManager.getConnection(
                    "jdbc:derby:" + Constants.DB_PATH + ";create=false", Constants.DB_USER, Constants.DB_PASSWORD);
        } catch (SQLException se) {
            // DB の自動生成
            conn = DriverManager.getConnection(
                    "jdbc:derby:" + Constants.DB_PATH + ";create=true", Constants.DB_USER, Constants.DB_PASSWORD);

            // DB初期化
            initDB(conn);

        }
        
        return conn;
    }
    
    /**
     * DBの初期化
     */
    private static void initDB(Connection conn) throws SQLException {
        
        initSiteTable();
        initOauthTable();
    }
    
    // -------------------------------------------------------------------------
    // サイトマスター
    // -------------------------------------------------------------------------
    /**
     * サイトマスター初期化
     * @throws SQLException 
     */
    private static void initSiteTable() throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("create table site_mst (");
        sql.append("app_id VARCHAR(255) NOT NULL");
        sql.append(",google_drive_key VARCHAR(255) NOT NULL");
        sql.append(",constraint PK_SITE_MST primary key (app_id)");
        sql.append(")");

        Statement smt = conn.createStatement();
        smt.execute(sql.toString());
    }
    
    /**
     * サイトマスター登録 or 更新
     * @param conn
     * @param appId
     * @param googleDriveKey
     * @throws SQLException 
     */
    public static void insertOrUpdateSiteMst(
            Connection conn, 
            String appId,
            String googleDriveKey) throws SQLException {
        
        // 既に存在するアカウントの場合はupdateし、処理を終了
        SiteMstEntity entity = getSiteEntity(conn, appId);
        if(entity != null) {
            updateSiteEntity(conn,entity);
            return;
        }
        
        StringBuilder sql = new StringBuilder();
        
        sql.append("insert into site_mst values(?,?)");

        PreparedStatement smt = conn.prepareStatement(sql.toString());
        smt.setString(1, appId);
        smt.setString(2, googleDriveKey);
        
        smt.executeUpdate();
    }
    
    /**
     * サイト情報取得
     * @param conn
     * @param appId
     * @return
     * @throws SQLException 
     */
    public static SiteMstEntity getSiteEntity(Connection conn, String appId) throws SQLException {
        String sql = "select * from site_mst where app_id=?";

        PreparedStatement smt = conn.prepareStatement(sql);
        smt.setString(1, appId);

        ResultSet rs = smt.executeQuery();
        SiteMstMapping mapping = new SiteMstMapping();

        while (rs.next()) {
            return mapping.createFromResultSet(rs);
        }

        return null;
    }
    
    /**
     * サイトマスター更新
     * @param conn
     * @param entity
     * @throws SQLException 
     */
    public static void updateSiteEntity(Connection conn, SiteMstEntity entity) throws SQLException {
        String sql = "update site_mst"
                + " set"
                + "   google_drive_key=?"
                + " where"
                + "   app_id=?";

        PreparedStatement smt = conn.prepareStatement(sql);
        
        smt.setString(1, entity.googleDriveKey);
        smt.setString(2, entity.appId);

        smt.executeUpdate();
    }
    
    /**
     * サイトマスター削除
     * @param conn
     * @param appId
     * @throws SQLException 
     */
    public static void deleteSite(Connection conn, String appId) throws SQLException {
        String sql = "delete from site_mst where app_id=?";

        PreparedStatement smt = conn.prepareStatement(sql);
        smt.setString(1, appId);

        smt.executeUpdate();
    }
    
    /**
     * サイトリストの取得
     * @param conn
     * @return
     * @throws SQLException 
     */
    public static List<SiteMstEntity> getSiteList(Connection conn)
            throws SQLException {
        String sql = "select * from site_mst";
        PreparedStatement smt = conn.prepareStatement(sql);

        ResultSet rs = smt.executeQuery();
        SiteMstMapping mapping = new SiteMstMapping();

        List<SiteMstEntity> accountList = new ArrayList();
        while (rs.next()) {
            accountList.add(mapping.createFromResultSet(rs));
        }

        return accountList;
    }
    
    
    
    // -------------------------------------------------------------------------
    // OAUTHマスター
    // -------------------------------------------------------------------------
    /**
     * OAUTHマスター初期化
     * @throws SQLException 
     */
    private static void initOauthTable() throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("create table oauth_mst (");
        sql.append("data_key VARCHAR(255) NOT NULL");
        sql.append(",mail_address VARCHAR(255) NOT NULL");
        sql.append(",user_id VARCHAR(255) NOT NULL");
        sql.append(",access_token VARCHAR(255) NOT NULL");
        sql.append(",refresh_token VARCHAR(255) NOT NULL");
        sql.append(",constraint PK_OAUTH_MST primary key (data_key)");
        sql.append(")");

        Statement smt = conn.createStatement();
        smt.execute(sql.toString());
    }
    
    /**
     * OAUTHマスターの登録 or 更新
     * @param conn
     * @param mailAddress
     * @param userId
     * @param accessToken
     * @param refreshToken
     * @throws SQLException 
     */
    public static void insertOrUpdateOauthMst(
            Connection conn, 
            String mailAddress, 
            String userId,
            String accessToken, 
            String refreshToken) throws SQLException {
        
        // 既に存在するアカウントの場合はupdateし、処理を終了
        OauthMstEntity entity = getOauthEntity(conn);
        if(entity != null) {
            updateOauthEntity(conn,entity);
            return;
        }
        
        StringBuilder sql = new StringBuilder();
        
        sql.append("insert into oauth_mst values(?,?,?,?,?)");

        PreparedStatement smt = conn.prepareStatement(sql.toString());
        smt.setString(1, OAUTH_DATA_KEY);
        smt.setString(2, mailAddress);
        smt.setString(3, userId);
        smt.setString(4, accessToken);
        smt.setString(5, refreshToken);
        
        smt.executeUpdate();
    }
    
    /**
     * OAUTH情報取得
     * @param conn
     * @return
     * @throws SQLException 
     */
    public static OauthMstEntity getOauthEntity(Connection conn) throws SQLException {
        String sql = "select * from oauth_mst where data_key=?";

        PreparedStatement smt = conn.prepareStatement(sql);
        smt.setString(1, OAUTH_DATA_KEY);

        ResultSet rs = smt.executeQuery();
        OauthMstMapping mapping = new OauthMstMapping();

        while (rs.next()) {
            return mapping.createFromResultSet(rs);
        }

        return null;
    }
    
    /**
     * OAUTH情報更新
     * @param conn
     * @param entity
     * @throws SQLException 
     */
    public static void updateOauthEntity(Connection conn, OauthMstEntity entity) throws SQLException {
        String sql = "update oauth_mst"
                + " set"
                + "   mail_address=?,"
                + "   user_id=?,"
                + "   access_token=?,"
                + "   refresh_token=?"
                + " where"
                + "   data_key=?";

        PreparedStatement smt = conn.prepareStatement(sql);
        
        smt.setString(1, entity.mailAddress);
        smt.setString(2, entity.userId);
        smt.setString(3, entity.accessToken);
        smt.setString(4, entity.refreshToken);
        smt.setString(5, OAUTH_DATA_KEY);

        smt.executeUpdate();
    }
    
    /**
     * OAUTH情報の削除
     * @param conn 
     * @throws java.sql.SQLException 
     */
    public static void deleteOauthInfo(Connection conn) throws SQLException {
        String sql = "delete from oauth_mst where data_key=?";

        PreparedStatement smt = conn.prepareStatement(sql);
        smt.setString(1, OAUTH_DATA_KEY);

        smt.executeUpdate();
    }
}
